﻿using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace Repository
{
    public class OutboundManagementRepository : IOutboundManagementRepository
    {
        private readonly string _connectionString;

        public OutboundManagementRepository(IConfiguration configuration)
        {
            // 从配置中读取连接字符串
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        public async Task<List<WMSOrderSalesOrder3>> GetAllWMSOutboundAsync(string sales_order_no = null, string document_status = null, string customer_name = null, DateTime? sales_date = null, string sales_person = null, string sales_dept = null)
        {
            string sql = @" SELECT
    so.sales_order_id AS 'sales_order_id',
    so.sales_order_no AS 'sales_order_no',
    so.document_status AS 'document_status',
    so.sales_date AS 'sales_date',
    so.note AS 'note',
    create_user.user_name AS 'create_user_name',  -- 避免与u.user_name重名，修改别名
    so.create_by AS 'create_by',
    so.create_time AS 'create_time',
    so.reviewer AS 'reviewer',
    so.review_time AS 'review_time',
    c.customer_name AS 'customer_name',
    c.contact_person AS 'contact_person',
    c.contact_phone_number AS 'contact_phone_number',
    org.organization_name AS 'org_name',  -- 原组织名称，避免混淆
    u.user_name AS 'sales_person',  -- 明确为销售负责人姓名
    SUM(sod.prod_num) AS 'prod_num',
    SUM(sod.saleamount) AS 'saleamount',
    STRING_AGG(pi.product_name, ',') AS 'product_name',
    create_org.organization_name AS 'create_department',  -- 明确为创建部门
    sales_org.organization_name AS 'sales_department'  -- 新增：销售部门名称
FROM
    dbo.wms_order_sales_order so
JOIN dbo.wms_basic_customer c ON so.customer_id = c.customer_id
JOIN dbo.sys_org_organizations org ON so.organizations_id = org.organization_id
JOIN dbo.sys_org_user_management u ON so.person_in_charge = u.user_id
JOIN dbo.sys_org_organizations sales_org ON u.organization_id = sales_org.organization_id  -- 新增：关联销售部门
JOIN dbo.wms_order_sales_order_details sod ON so.sales_order_id = sod.sales_order_id
JOIN dbo.wms_basic_product_information pi ON sod.product_code = pi.product_code
JOIN dbo.sys_org_user_management create_user ON so.create_by = create_user.user_id
JOIN dbo.sys_org_organizations create_org ON create_user.organization_id = create_org.organization_id
WHERE 1=1
    AND so.is_delete = 0
      -- 新增：状态必须为 6（未出库）或 7（部分出库）
    AND so.document_status IN (6, 7)
    -- 原条件保留
    AND (@SalesOrderNo IS NULL OR so.sales_order_no LIKE '%' + @SalesOrderNo + '%')  -- 模糊查询优化
    AND (@DocumentStatus IS NULL OR so.document_status = @DocumentStatus)
    AND (@CustomerName IS NULL OR c.customer_name LIKE '%' + @CustomerName + '%')  -- 模糊查询优化
    AND (@SalesDate IS NULL OR so.sales_date = @SalesDate)
    -- 新增：销售负责人（姓名模糊查询）
    AND (@SalesPerson IS NULL OR u.user_name LIKE '%' + @SalesPerson + '%')
    -- 新增：销售部门（部门名称模糊查询）
    AND (@SalesDept IS NULL OR sales_org.organization_name LIKE '%' + @SalesDept + '%')
GROUP BY
    so.sales_order_id,
    so.sales_order_no,
    so.document_status,
    so.sales_date,
    so.note,
    so.create_by,
    create_user.user_name,
    so.create_time,
    so.reviewer,
    so.review_time,
    c.customer_name,
    c.contact_person,
    c.contact_phone_number,
    org.organization_name,
    u.user_name,
    create_org.organization_name,
    sales_org.organization_name  -- 新增：销售部门分组";


            // 使用Dapper执行查询（需确保已注入IDbConnection）
            using (var connection =new SqlConnection(_connectionString) ) // 假设_dbConnection是数据库连接对象
            {
                var parameters = new
                {
                    SalesOrderNo = sales_order_no,
                    DocumentStatus = document_status,
                    CustomerName = customer_name,
                    SalesDate = sales_date,
                    SalesPerson = sales_person,
                    SalesDept = sales_dept
                };

                return (await connection.QueryAsync<WMSOrderSalesOrder3>(sql, parameters))
                    .ToList();
            }



        }

        public async Task<List<WMSOrderSalesOrder4>> GetOutboundById(string salesOrderNo)
        {
            var sql = @"
 SELECT 
    p.product_code AS product_code,
    p.product_name AS product_name,
    p.specification_model AS specification_model,
    d.DictLabel AS DictLabel,
    sod.selling_unit_price AS selling_unit_price,
    sod.prod_num AS prod_num,
    sod.saleamount AS saleamount,
    sod.note AS note,
    sod.outbound_num AS outbound_num,
    (i.current_inventory - sod.outbound_num) AS unoutbound,
    t.product_type_name AS product_type_name,
    i.current_inventory AS current_inventory
FROM 
    dbo.wms_order_sales_order_details sod
INNER JOIN dbo.wms_order_sales_order so 
    ON sod.sales_order_id = so.sales_order_id 
INNER JOIN dbo.wms_basic_product_information p 
    ON sod.product_code = p.product_code
INNER JOIN dbo.wms_basic_type_of_goods t 
    ON p.product_type_id = t.product_type_id
INNER JOIN dbo.wms_Dictionary_data d 
    ON p.unit = d.DictValue AND d.DictTypeCode = 'Unit'
INNER JOIN dbo.wms_knzy_inventory i 
    ON p.product_code = i.product_code
WHERE 
    sod.is_delete = '0' 
    AND so.sales_order_no = @sales_order_no"  ;

            using (var conn = new SqlConnection(_connectionString))
            {
                // 正确写法（参数名与SQL中的@sales_order_no保持一致）
                var result = await conn.QueryAsync<WMSOrderSalesOrder4>(sql, new { sales_order_no = salesOrderNo });
                return result.ToList();
            }
        }


    }
}
